Course: Microsoft Power BI Training
Topic: Creating Measures and Understanding Their Use
Course Link: Microsoft Power BI Training at JustAcademy
Demo Link: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
Measures are calculations used in Microsoft Power BI to summarize data and generate meaningful business insights. They are commonly created using Data Analysis Expressions (DAX), a formula language designed for analytical data models.
Measures help users calculate important business metrics such as total sales, total profit, average revenue, profit margin, customer counts, year-to-date sales, and sales growth. These calculations can be displayed in cards, charts, tables, matrices, and other report visuals.
One of the most important features of measures is that their results can change dynamically according to the filters applied to a report. For example, a Total Sales measure can display overall sales, sales for a particular region, or sales for a selected year without requiring a separate formula for each situation.
A measure is a named calculation that returns a result when Power BI evaluates it in a particular filter context. Measures are usually written in DAX and are evaluated when a visual or query requests their values.
For example, a business may have a Sales table containing thousands of transaction records. Instead of manually adding all the sales amounts, a measure can calculate the total automatically.
Total Sales =
SUM(Sales[SalesAmount])Here, Total Sales is the measure name, SUM is the DAX function, and Sales[SalesAmount] identifies the column containing sales values.
A measure does not usually store a separate calculated result for every row. Instead, Power BI evaluates the measure when needed, based on the data and filters relevant to the current visual.
Suppose a company has the following sales data:
| Order ID | Region | Product | Sales Amount |
|---|---|---|---|
| 1001 | West | Laptop | 5000 |
| 1002 | East | Keyboard | 1200 |
| 1003 | West | Monitor | 2500 |
| 1004 | North | Laptop | 4000 |
| 1005 | East | Mouse | 800 |
Create the following measure:
Total Sales =
SUM(Sales[Sales Amount])The measure returns the following results under different filter contexts:
| Filter Context | Total Sales |
|---|---|
| All regions | 13,500 |
| West region | 7,500 |
| East region | 2,000 |
| North region | 4,000 |
The same measure produces different results because the applicable data changes with the filter context.
Power BI supports different ways to obtain aggregated values and create custom calculations.
An implicit measure is an aggregation that Power BI creates automatically when a numeric column is added to a visual and summarized. For example, dragging Sales[Sales Amount] into a chart may produce Sum of Sales Amount.
Common automatic aggregations include:
Implicit measures are convenient for quick analysis. However, they provide less control over reusable business logic than explicitly defined model measures.
An explicit measure is created deliberately using the New measure command and a DAX expression. It has a name that can be reused throughout the report.
Total Sales =
SUM(Sales[Sales Amount])Explicit measures are generally preferred for important business metrics because they provide consistent definitions, support more complex calculations, and can be referenced by other measures.
Quick measures use a guided interface to generate DAX formulas for common calculations. They can be useful when learning DAX or building standard calculations without writing every formula manually.
After creating a quick measure, inspect its generated DAX formula to understand how the calculation works and adapt it when necessary.
| Feature | Implicit Measure | Explicit Measure | Quick Measure |
|---|---|---|---|
| Creation | Automatically through visual aggregation | Created using a DAX formula | Created through a guided interface |
| Custom logic | Limited to available aggregations | Highly customizable | Depends on the selected calculation |
| Reusable definition | Usually tied to a visual's field aggregation | Reusable across visuals | Reusable after creation as a measure |
| Best use | Quick exploratory analysis | Business-critical calculations | Common calculations and learning DAX |
Power BI Desktop provides several ways to create a measure. The following process explains how to create a standard explicit measure.
Open Power BI Desktop and load a dataset containing the required columns. For example, a Sales table might include Sales Amount, Cost Amount, Quantity, Order ID, and transaction date.
In the Data or Fields pane, locate the table that contains the columns needed for the calculation. You can organize the measure in the Sales table or another appropriate table in the model.
Right-click the selected table and choose New measure. Alternatively, select the table and choose New measure from the ribbon.
Enter a meaningful measure name and a valid DAX expression in the formula bar.
Total Sales =
SUM(Sales[Sales Amount])Press Enter or use the available commit control. Power BI validates the expression and adds the measure to the model when the formula is valid.
DAX formulas combine function names, column references, measure references, operators, constants, and filter expressions.
Measure Name =
DAX_ExpressionExample:
Total Quantity =
SUM(Sales[Quantity])A column reference typically includes the table name followed by the column name in square brackets.
SUM(Sales[Sales Amount])Table-qualified references make formulas easier to understand and help distinguish columns with similar names in different tables.
Measures can reference other measures by using the measure name in square brackets.
Total Profit =
[Total Sales] - [Total Cost]Reusing existing measures helps maintain a consistent definition of sales, costs, and profit across the model.
Measures can answer a wide variety of business questions. The following examples use a hypothetical Sales table with Sales Amount, Cost Amount, Quantity, Order ID, and Customer ID columns.
Total Sales =
SUM(Sales[Sales Amount])This measure calculates total sales for the current filter context.
Total Cost =
SUM(Sales[Cost Amount])This measure adds the cost amounts associated with the currently selected records.
Total Profit =
[Total Sales] - [Total Cost]This measure calculates profit as sales minus cost. The formula assumes that the cost definition is appropriate for the business's profit calculation.
Profit Margin =
DIVIDE(
[Total Profit],
[Total Sales],
0
)Profit margin expresses profit as a proportion of sales. Format the measure as a percentage in Power BI.
Total Quantity =
SUM(Sales[Quantity])This measure calculates the total quantity sold in the current context.
Total Orders =
DISTINCTCOUNT(Sales[Order ID])This measure counts unique order identifiers. It is useful when one order can have multiple rows in the Sales table.
Unique Customers =
DISTINCTCOUNT(Sales[Customer ID])This measure counts distinct customer identifiers present in the filtered Sales table. If the model has a dedicated customer dimension, a measure based on that dimension may be more appropriate for certain customer analyses.
Average Order Value =
DIVIDE(
[Total Sales],
[Total Orders],
0
)This measure calculates average sales per distinct order. It is not the same as average transaction-row value when orders contain multiple product lines.
Average Sales Amount =
AVERAGE(Sales[Sales Amount])This measure returns the average nonblank sales amount per row, not necessarily the average amount per order.
Filter context is one of the most important concepts in understanding how measures work. It refers to the filters that affect the data included in a calculation.
Filters can come from:
Consider the measure:
Total Sales =
SUM(Sales[Sales Amount])When placed in a card without additional filters, the measure displays total sales across the available data. When the report is filtered to the West region, it displays sales for the West region, assuming the model's relationships and filters are configured correctly.
| Selected Region | Sales Included | Total Sales |
|---|---|---|
| West | West transactions | 7,500 |
| East | East transactions | 2,000 |
| North | North transactions | 4,000 |
| All regions | All transactions | 13,500 |
The measure remains the same. The result changes because the filter context changes.
Measures and calculated columns are both useful in Power BI, but they solve different problems.
Measures are evaluated when a visual or query requests a result. They generally respond to the current filter context and are ideal for dynamic aggregations.
Total Sales =
SUM(Sales[Sales Amount])Calculated columns create stored values for individual rows during model processing or refresh. They are useful when each record needs a persistent attribute for grouping, filtering, sorting, or other modeling tasks.
Row Profit =
Sales[Sales Amount] - Sales[Cost Amount]| Feature | Measure | Calculated Column |
|---|---|---|
| Evaluation | When required by a query or visual | During model processing or refresh |
| Storage | Result generally not stored for every row | Values stored in the model |
| Responds dynamically to slicers | Usually yes | Stored values do not recalculate for each slicer selection |
| Common purpose | Totals, ratios, percentages, and business metrics | Row-level classifications and derived attributes |
| Example | Total Sales | Row Profit |
Measures become useful when placed in report visuals that present business metrics in a meaningful format.
A card displays a single prominent value, such as Total Sales, Total Profit, or Unique Customers.
Example: Add Total Sales to a card to display the total sales value for the current report context.
A table can display dimensions and measures together.
Example fields:
Each product row evaluates the measures in the context of that product.
A matrix is useful for hierarchical analysis, such as sales by region and product category. It can display subtotals and grand totals as well as individual group values.
A bar chart can compare sales or profit across products, categories, customers, or regions.
Example: Place Product Category on the axis and Total Sales in the values field.
A line chart can show how sales change over time.
Example: Place Date or Month on the X-axis and Total Sales on the Y-axis.
Slicers let users select values such as year, region, product category, or customer segment. Measures recalculate according to the selections when the slicer filters the relevant data.
CALCULATE is an important DAX function that evaluates an expression under a modified filter context. It helps create measures for specific business conditions.
CALCULATE(
Expression,
Filter1,
Filter2
)Electronics Sales =
CALCULATE(
[Total Sales],
Product[Category] = "Electronics"
)This measure calculates sales for the Electronics category while retaining other applicable filters, such as the selected year or region.
West Sales =
CALCULATE(
[Total Sales],
Sales[Region] = "West"
)This example assumes that Region is a column in the Sales table. If Region belongs to a separate dimension, reference the appropriate dimension column instead.
Sales All Categories =
CALCULATE(
[Total Sales],
REMOVEFILTERS(Product[Category])
)This measure removes the filter on Product[Category] while retaining other applicable filters. It can be used to compare one category against the overall category total within the remaining filter context.
Percentage and ratio measures help businesses understand relative performance rather than relying only on absolute numbers.
Profit Margin =
DIVIDE(
[Total Profit],
[Total Sales],
0
)Format the measure as a percentage. A profit margin of 0.25 is displayed as 25% when the percentage format is applied.
Sales Contribution % =
DIVIDE(
[Total Sales],
CALCULATE(
[Total Sales],
REMOVEFILTERS(Product[Category])
),
0
)This measure calculates the selected category's sales as a proportion of sales across all categories, while preserving other applicable filters. The exact result depends on the model's relationships and report context.
Sales Previous Year =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(DimDate[Date])
)
YoY Growth % =
DIVIDE(
[Total Sales] - [Sales Previous Year],
[Sales Previous Year],
0
)This example assumes a suitable date dimension and relationship. It compares current-period sales with the corresponding period in the previous year.
Formatting determines how a measure's result is displayed in a report. Correct formatting improves readability and prevents users from misinterpreting values.
| Measure | Recommended Format | Example Display |
|---|---|---|
| Total Sales | Currency | ₹13,500.00 |
| Total Quantity | Whole number | 250 |
| Profit Margin | Percentage | 25.00% |
| Average Order Value | Currency | ₹2,700.00 |
| Unique Customers | Whole number | 450 |
The displayed values above are illustrative. Select a suitable currency symbol, decimal precision, and display-unit setting according to the report's requirements.
As a model grows, it may contain dozens or hundreds of measures. Organizing them makes the model easier to understand and maintain.
Prefer names such as Total Sales, Total Profit, Profit Margin, and Sales Previous Year instead of generic names such as Measure 1 or Calculation 2.
You can create a dedicated table to store measures. This approach helps keep calculations organized separately from source data columns.
One approach is to use Enter data to create a small helper table, move measures to that table using the Home table property, and hide the helper column while leaving the table visible.
Display folders can group measures into logical categories, such as:
Instead of repeating the same sales calculation in every formula, create a Total Sales measure and reference it in other measures. This improves consistency and makes formulas easier to update.
Measures can be used as building blocks for more advanced calculations.
Total Profit =
[Total Sales] - [Total Cost]
Profit Margin =
DIVIDE(
[Total Profit],
[Total Sales],
0
)If the Total Sales or Total Cost definition changes, dependent measures can use the updated calculation automatically.
Variables can make a formula easier to read by naming intermediate results.
Total Profit =
VAR Revenue = [Total Sales]
VAR Expenses = [Total Cost]
RETURN
Revenue - ExpensesVAR defines a variable, and RETURN specifies the expression that produces the final result.
Ranking measures can order products, customers, or regions according to a selected business metric.
Product Sales Rank =
RANKX(
ALL(Product[Product Name]),
[Total Sales],
,
DESC,
DENSE
)This measure ranks products by Total Sales from highest to lowest. ALL removes the filter on Product Name for the ranking comparison while other applicable filters can remain. If a product slicer or other filters should affect the ranking differently, adjust the filter-removal logic to match the requirement.
Measures can generate text values for use in certain dynamic visual titles and other supported formatting scenarios.
Sales Report Title =
"Total Sales: " & FORMAT([Total Sales], "#,##0")Use the conditional formatting or title configuration options of the relevant visual to connect the measure to a dynamic title when supported.
Build a sales performance dashboard to practice creating and using measures in a realistic business scenario.
A retail business wants to track sales revenue, profit, profitability, order volume, customer activity, and performance across regions and product categories.
Prepare a Sales table containing the following fields:
For a more complete model, create related Product, Customer, and Date dimension tables.
Total Sales =
SUM(Sales[Sales Amount])
Total Cost =
SUM(Sales[Cost Amount])
Total Profit =
[Total Sales] - [Total Cost]
Profit Margin =
DIVIDE([Total Profit], [Total Sales], 0)
Total Orders =
DISTINCTCOUNT(Sales[Order ID])
Unique Customers =
DISTINCTCOUNT(Sales[Customer ID])
Total Quantity =
SUM(Sales[Quantity])
Average Order Value =
DIVIDE([Total Sales], [Total Orders], 0)Problem: Power BI reports an error because the formula refers to a missing or incorrectly named column.
Solution: Verify the table and column names in the Data pane. Use the DAX formula editor's suggestions where possible.
Problem: A measure references a column that contains multiple values in the current context without specifying how one value should be obtained.
Solution: Use an appropriate aggregation such as SUM or MAX, or use SELECTEDVALUE when the business logic requires a single selected value.
Selected Category =
SELECTEDVALUE(
Product[Category],
"Multiple or No Categories"
)Problem: A ratio measure returns an unexpected result because the denominator is zero or blank.
Solution: Use DIVIDE and choose a suitable alternate result.
Profit Margin =
DIVIDE(
[Total Profit],
[Total Sales],
BLANK()
)Problem: The total displayed in a table or matrix does not equal the sum of its visible row-level results.
Solution: Understand that measures are recalculated in the total's filter context. If the required business definition is the sum of a row-level expression, consider an appropriate iterator such as SUMX.
Problem: Changing a slicer does not change the expected measure result.
Solution: Check the slicer's interactions with the visual, the model relationships, the direction of filter propagation, and any filters explicitly removed by the DAX formula.
Problem: A percentage measure displays 0.25 instead of 25% or displays an unexpectedly large number.
Solution: Format the measure as Percentage and verify that the formula returns a ratio rather than multiplying the ratio by 100 unnecessarily.
Answer: A measure is a named calculation, usually written in DAX, that returns a result based on the current filter context when a query or visual evaluates it.
Answer: Measures provide reusable business calculations that respond dynamically to filters and enable consistent reporting across multiple visuals.
Answer: An implicit measure is an automatic aggregation created when a field is summarized in a visual. An explicit measure is a named DAX calculation deliberately created using the New measure command.
Answer: Select a table in Power BI Desktop, choose New measure, enter a DAX formula, validate it, and add the measure to a report visual.
Answer: Filter context is the collection of filters that determines which data is considered during a calculation. It can be influenced by slicers, visual fields, report filters, relationships, and DAX expressions.
Answer: A measure is evaluated when required and typically responds dynamically to filters. A calculated column stores a value for each row during model processing or refresh.
Answer: CALCULATE evaluates an expression in a modified filter context and is frequently used to create conditional calculations.
Answer: DIVIDE performs division while allowing an alternate result for a zero or blank denominator, making ratio calculations easier to handle safely.
Answer: Yes. For example, Total Profit can reference Total Sales and Total Cost. Reusing measures helps keep business logic consistent.
Answer: The slicer changes the filter context, so Power BI recalculates the measure using the data that remains relevant to that context.
Answer: A quick measure is a guided calculation that generates a DAX formula for a common analytical requirement.
Answer: Measures can be organized with meaningful names, dedicated measure tables, display folders, and consistent naming conventions.
Answer: Power BI usually reevaluates the measure in the total's filter context. It does not necessarily add the individual row-level measure results.
Answer: RANKX evaluates an expression across a table and assigns rankings based on the results. It can rank products, customers, or regions according to a measure.
Answer: Check the formula, source columns, relationships, filter context, aggregation logic, data types, formatting, and expected business definition. Compare the output with trusted source data.
Creating measures and understanding their use is a fundamental skill in Microsoft Power BI. Measures help transform raw data into meaningful business metrics, including sales, profit, profit margin, average order value, order counts, and growth rates.
The most important concepts are creating explicit measures with DAX, understanding filter context, choosing the correct aggregation, reusing measures, and testing calculations under different report filters. As you progress, functions such as CALCULATE, DIVIDE, RANKX, and time intelligence functions will help you build more advanced analytical reports.
By practicing these techniques with realistic datasets, you can create reliable, interactive dashboards and maintain consistent business calculations throughout your Power BI reports.
Explore Microsoft Power BI Training: Visit JustAcademy's Microsoft Power BI Training course, register for a course demo, or visit the JustAcademy website.
